package io.zhifei.common.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.springframework.mock.web.MockMultipartFile;

/**
 * <p>功能描述: excel工具类</p>
 *
 * @author : duke
 * @version 1.0.0<br>
 * @ClassName: com.zhifei.bus.core.util.class
 * @CreateTime 2020/04/13
 */
public class ExcelUtil {

    public static void solveRequestHead(String fileName, HttpServletResponse response, HttpServletRequest request)throws IOException {
        //获得浏览器代理信息
        final String userAgent = request.getHeader("USER-AGENT");
        //判断浏览器代理并分别设置响应给浏览器的编码格式
        String finalFileName = null;
        if (StringUtils.contains(userAgent, "MSIE") || StringUtils.contains(userAgent, "Trident")) {//IE浏览器
            finalFileName = URLEncoder.encode(fileName, "UTF8");
            System.out.println("IE浏览器");
        } else if (StringUtils.contains(userAgent, "Mozilla")) {//google,火狐浏览器
            finalFileName = new String(fileName.getBytes(), "ISO8859-1");
        } else {
            finalFileName = URLEncoder.encode(fileName, "UTF8");//其他浏览器
        }
        response.setContentType("application/x-download");//告知浏览器下载文件，而不是直接打开，浏览器默认为打开
        response.addHeader("Content-Disposition", "attachment;filename=\"" + finalFileName + "\"");//下载文件的名称

    }


    /**
     *  <p> 获取对应为的合并行 </p>
     * @param sheet - <br/>
     * @param firRow - <br/>
     * @param secRow - <br/>
     * @param firColumn - <br/>
     * @param secColumn -  <br/>
     * @return int <br/>
     * @author: tt
     * @date 2020/4/13 10:14 <br/>
     * @throws
     */
    public static int getMergedRegionIndex(Sheet sheet, int firRow, int secRow, int firColumn, int secColumn) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (firRow == firstRow && secRow == lastRow) {
                if (firColumn == firstColumn && secColumn == lastColumn) {
                    return i;
                }
            }
        }
        return 0;
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    public static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case HSSFCell.CELL_TYPE_NUMERIC:
                case HSSFCell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        cellvalue = sdf.format(date);
                    }
                    // 如果是纯数字
                    else {
                        // 取得当前Cell的数值
                        //DecimalFormat formatter = new DecimalFormat("#");
                        //cellvalue = formatter.format(cell.getNumericCellValue());
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                // 如果当前Cell的Type为STRIN
                case HSSFCell.CELL_TYPE_STRING:
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                // 默认的Cell值
                default:
                    cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        //验证是否有tab空格
        if (cellvalue.contains("\uDBC0\uDC2B")){
            return "";
        }
        return cellvalue.trim();
    }
    /**
     * @return : org.springframework.web.multipart.MultipartFile -
     * @<p>功能描述：根据path获取MultipartFile</p>
     * @param : path - 文件路径
     * @param : name -文件名称
     * @param : fileSuffix - 文件后缀名
     * @author: niemc
     * @date: 2020/7/14 15:33
     */
    public static MultipartFile getMultipartFile(String path, String name, String fileSuffix) throws IOException {
        MultipartFile titleFile = null;
        InputStream inputStream = null;
        URL url = new URL(path);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        //设置超时间为3秒
        conn.setConnectTimeout(3 * 1000);
        //防止屏蔽程序抓取而返回403错误
        conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
        //得到输入流
        inputStream = conn.getInputStream();

        titleFile = new MockMultipartFile(name, name, fileSuffix, inputStream);

        return titleFile;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public static String getMergedRegionValue(XSSFSheet sheet , int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();

        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if(row >= firstRow && row <= lastRow){

                if(column >= firstColumn && column <= lastColumn){
                    XSSFRow fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellFormatValue(fCell) ;
                }
            }
        }

        return null ;
    }


}
